﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace WinLogicUniversity
{
    class PurchaseList
    {
        static LogicUniversityT2Entities logic = new LogicUniversityT2Entities();

        public static List<PurchaseObj> getPurchaseList()
        {
            var plist = from itemDetail in logic.ItemDetails
                        join item in logic.Items on itemDetail.ItemID equals item.ItemID
                        where itemDetail.Item.ReorderLevel >= itemDetail.Item.StockBalance //&& supID.Contains(item.FirstSupplier)
                        let Item_No = itemDetail.ItemID
                        let Item_Description = itemDetail.Item.ItemDescription
                        let first_Name =item.FirstSupplier
                        let Quantity = (item.ReorderLevel.Value + item.ReorderQuantity.Value)
                        group itemDetail by new
                        {
                            Item_No,
                            Item_Description,
                            first_Name,
                            Quantity,
                        } into grouped
                        select new
                        {
                            grouped.Key.Item_No,
                            grouped.Key.Item_Description,
                            grouped.Key.first_Name,
                            //Supplier_Name=grouped.Select(name=>grouped.Key.first_Name).Distinct().ToString(),
                            grouped.Key.Quantity,
                            Price = grouped.Average(p => p.Price),
                            Amount_Item = (grouped.Average(p => p.Price)) * grouped.Key.Quantity
                        };
            List<PurchaseObj> list = new List<PurchaseObj>();
            foreach (var i in plist)
            { PurchaseObj p = new PurchaseObj();
            p.ItemNo = i.Item_No;
            p.ItemDescription = i.Item_Description;
            p.SupplierName = i.first_Name;
            p.Qty = i.Quantity;
            p.Price = Convert.ToDouble(i.Price);
            p.Amount =Convert.ToDouble(i.Amount_Item);
            list.Add(p);
            }
            return list;
        }

        public static List<PurchaseObj> addPurchaseList(List<PurchaseObj>list)
        {
            List<PurchaseObj> plist = list;
            string DumSup = "Dummy_Supplier";
            string id = "";
             for (int i = 0; i < plist.Count; i++)
                {
                    string sup = plist[i].SupplierName;
                    var supName = (from supp in logic.Suppliers where supp.SupplierName==sup select new { sID = supp.SupplierID });
                    foreach (var sid in supName)
                    {
                        plist[i].SupplierName = sid.sID;
                    }
                    PurchaseOrder purchaseOrder = new PurchaseOrder();
                    purchaseOrder.PurchaseOrderID = AutoGenPurchaseOrderID().ToString();
                    Supplier supEntity = new Supplier();
                    if (DumSup != plist[i].SupplierName)
                    {
                        purchaseOrder.Date = plist[i].Date;// DateTime.Parse(lblDate.Text);
                        purchaseOrder.SupplierID = plist[i].SupplierName;
                        purchaseOrder.Status = "Order";
                        logic.AddToPurchaseOrders(purchaseOrder);
                        if (plist[i].ItemNo != null)
                        {
                            PurchaseDetail purchaseDetail = new PurchaseDetail();
                            purchaseDetail.PurchaseDetailID = AutoGenPurchaseOrderDetailID().ToString();
                            purchaseDetail.PurchaseOrderID = purchaseOrder.PurchaseOrderID;
                            purchaseDetail.ItemID = plist[i].ItemNo;
                            purchaseDetail.OrderedQuantity = Convert.ToInt32(plist[i].Qty);
                            purchaseDetail.Amount = Convert.ToDecimal(plist[i].Amount);
                            //purchaseDetail.Date =Convert.ToDateTime("");
                            purchaseDetail.ReceivedQuantity = 0;
                            //logic.AddToPurchaseDetails(purchaseDetail);
                            logic.AddToPurchaseDetails(purchaseDetail);
                            logic.SaveChanges();
                            id = purchaseOrder.PurchaseOrderID.ToString();
                        }
                        logic.SaveChanges();
                        DumSup = purchaseOrder.SupplierID;
                    }
                    else
                    {
                        if (plist[i].ItemNo != null)
                        {
                            PurchaseDetail purchaseDetail = new PurchaseDetail();
                            purchaseDetail.PurchaseDetailID = AutoGenPurchaseOrderDetailID().ToString();
                            purchaseDetail.PurchaseOrderID = id.ToString();
                            purchaseDetail.ItemID = plist[i].ItemNo;
                            purchaseDetail.OrderedQuantity = Convert.ToInt32(plist[i].Qty);
                            purchaseDetail.Amount = Convert.ToDecimal(plist[i].Amount);
                            logic.AddToPurchaseDetails(purchaseDetail);
                            logic.SaveChanges();
                        }
                    }
                }
             
            return null;
            }

        public static decimal getTotal()
        {
            decimal totalAmount = 0, amount = 0;
            var plist = from itemDetail in logic.ItemDetails
                        join item in logic.Items on itemDetail.ItemID equals item.ItemID
                        where itemDetail.Item.ReorderLevel >= itemDetail.Item.StockBalance //&& supID.Contains(item.FirstSupplier)
                        let Item_No = itemDetail.ItemID
                        let Item_Description = itemDetail.Item.ItemDescription
                        let first_Name = item.FirstSupplier
                        let Quantity = (item.ReorderLevel.Value + item.ReorderQuantity.Value)
                        group itemDetail by new
                        {
                            Item_No,
                            Item_Description,
                            first_Name,
                            Quantity,
                        } into grouped
                        select new
                        {
                            grouped.Key.Item_No,
                            grouped.Key.Item_Description,
                            grouped.Key.first_Name,
                            //Supplier_Name=grouped.Select(name=>grouped.Key.first_Name).Distinct().ToString(),
                            grouped.Key.Quantity,
                            Price = grouped.Average(p => p.Price),
                            Amount_Item = (grouped.Average(p => p.Price)) * grouped.Key.Quantity
                        };
            List<PurchaseObj> list = new List<PurchaseObj>();
            foreach (var i in plist)
            {
                PurchaseObj p = new PurchaseObj();
                p.ItemNo = i.Item_No;
                p.ItemDescription = i.Item_Description;
                p.SupplierName = i.first_Name;
                p.Qty = i.Quantity;
                p.Price = Convert.ToDouble(i.Price);
                p.Amount = Convert.ToDouble(i.Amount_Item);
                list.Add(p);
            }
            foreach (var total in plist)
            {
                amount = Convert.ToDecimal(total.Amount_Item);
                totalAmount += amount;
            }
            return totalAmount;
        }

        public static IQueryable getAllCat()
        {
            var cat = from cate in logic.Categories select new { cID = cate.CategoryID, cName =cate.CategoryName };
            return cat;
        }
        public static IQueryable getAllItem(string c)
        {
            string cid = c;
            var item = from items in logic.Items where items.CategoryID==cid select new { iID = items.ItemID, iName = items.ItemDescription};
            return item;
        }
        public static string getItemName(string c)
        {
            string name = "";
            string cid = c;
            var item = from items in logic.Items where items.ItemID == cid select new { iID = items.ItemID, iName = items.ItemDescription };
            foreach (var i in item)
            {
                name = i.iName;
            }
            return name;
        }

        public static IQueryable getAllSupplierName()
        {
            var item = from sup in logic.Suppliers select new { sID = sup.SupplierID, sName = sup.SupplierName };
            return item;
        }
        public static string getSupName(string c)
        {
            string cid = c;
            string n="";
            var item = from sup in logic.Suppliers where sup.SupplierName == cid select new { iID =sup.SupplierID, iName = sup.SupplierName };
            foreach (var name in item)
            { n = name.iName; }
            return n;
        }
        public static decimal getPrice(string c)
        {
            string cid = c;
            decimal price=0;
            var item =from items in logic.ItemDetails where items.ItemID==cid select new { itemprice=items.Price};
            foreach (var p in item)
            {
                price = Convert.ToDecimal(p.itemprice);
            }
            return price;
        }
        public static string AutoGenPurchaseOrderID()
        {
            int intId = 0; string POID;
            try
            {
                intId = Convert.ToInt32(logic.PurchaseOrders.Max(g => g.PurchaseOrderID).Substring(2));
            }
            catch (NullReferenceException)
            {
                POID = "PO0001";
                return POID;
            }
            finally
            {
                int MaxPOID = Convert.ToInt32(intId) + 1;
                POID = string.Format("PO{0:0000}", MaxPOID);
            }
            return POID;
        }

        public static string AutoGenPurchaseOrderDetailID()
        {
            int intId = 0; string PODID;
            try
            {
                intId = Convert.ToInt32(logic.PurchaseDetails.Max(g => g.PurchaseDetailID).Substring(2));
            }
            catch (NullReferenceException)
            {
                PODID = "PD0001";
                return PODID;
            }
            finally
            {
                int MaxPODID = Convert.ToInt32(intId) + 1;
                PODID = string.Format("PD{0:0000}", MaxPODID);
            }
            return PODID;
        }
    }
}
